﻿use KHACHSAN
go

CREATE TABLE TANG
(
		ID_Tang int identity(1,1) Not NULL,
		TenTang NVARCHAR(500) Not Null,
		MAKV nchar(4)
		Constraint PK_TANG primary key(ID_TANG),
		Constraint FK_TANG_KHUVUC foreign key(MAKV) References KHUVUC(MAKV)
)
-- 1 KHU VUC CÓ NHIEU TANG, MOI TANG CO NHIEU PHONG
go
Create table NHOMQUYEN
(
		MaNhom INT identity(1,1) Not NULL,
		TenNhom NVARCHAR(500) Not Null
		Constraint PK_NHOMQUYEN primary key(MaNhom)
)
go

Create table CHUCNANG
(
		MaChucNang INT identity(1,1) Not NULL,
		TenChucNang NVARCHAR(500) Not Null
		Constraint PK_CHUCNANG primary key(MaChucNang)	
)
go

Create table CHUCNANG_NHOMQUYEN
(
	MaNhom int,
	MaChucNang int
	Constraint PK_NHOMQUYEN_CHUCNANG primary key(MaNhom,MaChucNang),
	Constraint FK_CHUCNANG_NHOMQUYEN_CHUCNANG foreign key(MaChucNang) References CHUCNANG(MaChucNang),
	Constraint FK_CHUCNANG_NHOMQUYEN_NHOMQUYEN foreign key(MaNhom) References NHOMQUYEN(MaNhom)
)
go

create table TAIKHOANDANGNHAP
(
		TenDangNhap NVARCHAR(50) Not NULL,
		MatKhau NVARCHAR(50) Not Null,
		MatKhau2 Nvarchar(50),
		MaNV nchar(10),
		MaNhom INT,
		KichHoat bit default(1),
		Constraint PK_TAIKHOANDANGNHAP primary key(TenDangNhap),
		Constraint FK_TAIKHOANDANGNHAP_NHOMQUYEN foreign key(MaNhom) References NHOMQUYEN(MaNhom),
		Constraint FK_TAIKHOANDANGNHAP_NHANVIEN foreign key(MANV) References NHANVIEN(MANV)
)
go
create table CALAMVIEC
(
		ID_CLV int identity(1,1) Not NULL ,
		TenCa NVARCHAR(50) Not Null,
		TuGio int,
		DenGio int,
		Constraint PK_CALAMVIEC primary key(ID_CLV)
)
go
CREATE TABLE Log_DangNhap(
	ID_DN int identity(1,1) NOT NULL,
	TenDangNhap nvarchar(50) NOT NULL,
	ID_CLV int NOT NULL,
	Ngay datetime NOT NULL
	,Constraint PK_Log_DangNhap primary key (ID_DN),
	Constraint FK_Log_DangNhap_CaLamViec foreign key(ID_CLV) References CaLamViec(ID_CLV),
	Constraint FK_Log_DangNhap_TaiKhoanDangNhap foreign key(TenDangNhap) References TaiKhoanDangNhap(TenDangNhap)
)
go

create proc sp_ThemTaiKhoanDangNhap
	@TenDangNhap nvarchar(50),
	@MatKhau nvarchar(50),
	@MaNV nchar(5),
	@MaNhom int,
	@MatKhau2 nvarchar(50),
	@KichHoat bit
as
begin
	insert into TaiKhoanDangNhap(TenDangNhap,MatKhau,MaNV,MaNhom,MatKhau2,KichHoat)values(@TenDangNhap,@MatKhau,@MaNV,@MaNhom,@MatKhau2,@KichHoat)
end

go

create proc sp_SuaTaiKhoanDangNhap
	@TenDangNhap nvarchar(50),
	@MatKhau nvarchar(50),
	@MaNV nchar(5),
	@MaNhom int,
	@MatKhau2 nvarchar(50),
	@KichHoat bit
as
begin
	update TaiKhoanDangNhap
	set
	MatKhau=@MatKhau,MaNV=@MaNV,MaNhom=@MaNhom,MatKhau2=@MatKhau2,KichHoat=@KichHoat
	Where TenDangNhap=@TenDangNhap
end
go

create proc sp_SuaTaiKhoanDangNhap
	@TenDangNhap nvarchar(50),
	@MatKhau nvarchar(50),
	@MaNV nchar(5),
	@MaNhom int,
	@MatKhau2 nvarchar(50),
	@KichHoat bit
as
begin
	update TaiKhoanDangNhap
	set
	MatKhau=@MatKhau,MaNV=@MaNV,MaNhom=@MaNhom,MatKhau2=@MatKhau2,KichHoat=@KichHoat
	Where TenDangNhap=@TenDangNhap
end

go

create proc sp_XoaTaiKhoanDangNhap
	@TenDangNhap nvarchar(50)
as
begin
	delete TaiKhoanDangNhap
	Where TenDangNhap=@TenDangNhap
end

go
create proc sp_Login
@TenDangNhap nvarchar(50),
@MatKhau nvarchar(50),
@Ngay varchar(20),
@ID_CLV int
as
begin
	/*
	Kích hoạt: 1 đang kích hoạt
			   0 chưa kích hoạt
	*/
	select * 
	from  TAIKHOANDANGNHAP join NHOMQUYEN on TAIKHOANDANGNHAP.MaNhom=NHOMQUYEN.MaNhom
	join NHANVIEN on TAIKHOANDANGNHAP.MaNV= NHANVIEN.MANV
	where TenDangNhap=@TenDangNhap and MatKhau=@MatKhau
	if(@@ROWCOUNT>0)
	begin
		set dateformat 'dmy'
		insert into log_DangNhap(TenDangNhap,ID_CLV,Ngay)values(
		@TenDangNhap,@ID_CLV,@Ngay)
	end
end

go
create proc sp_LayDanhSachChucNang
@MaNhom int
as
begin
	select * from CHUCNANG_NHOMQUYEN
	where MaNhom=@MaNhom
end
go

create proc sp_LayThongTinSoDoPhong
@ID_Tang int
as
begin
	select MAPH,Phong.MAGIA,Phong.MATTP,Phong.ID_Tang,TenTang,TRANGTHAI,Mau,GiaPhong.MAHANGPHONG,GIAPHONG.DIENGIAI as DienGiaiGiaPhong,
	TUNGAY,DENNGAY,GIA1KHACH,GIA2KHACH,GIA3KHACH,GIA4KHACH,GIATHEMNGLON,GIATHEMTREEM,LOAIPHONG.MALP,KIEUPHONG.MAKP,LOAIPHONG.LOAIPH
	,KIEUPHONG.DIENGIAI as DienGiaiKieuPhong,MAKV
	from PHONG join TRANGTHAIPHONG on PHONG.MATTP=TRANGTHAIPHONG.MATTP
	join GIAPHONG on PHONG.MAGIA=GIAPHONG.MAGIA
	join LOAI_KIEUPHONG on GIAPHONG.MAHANGPHONG=LOAI_KIEUPHONG.MAHANGPHONG
	join LOAIPHONG on LOAI_KIEUPHONG.MALP=LOAIPHONG.MALP
	join KIEUPHONG on LOAI_KIEUPHONG.MAKP=KIEUPHONG.MAKP
	join TANG on PHONG.ID_Tang=TANG.ID_Tang
	where PHONG.ID_Tang=@ID_Tang
end
go

----------------------

INSERT [dbo].[TRANGTHAIPHONG] ([MATTP], [TRANGTHAI]) VALUES (1, N'Đang trống')
INSERT [dbo].[TRANGTHAIPHONG] ([MATTP], [TRANGTHAI]) VALUES (2, N'Đã có khách đặt')
INSERT [dbo].[TRANGTHAIPHONG] ([MATTP], [TRANGTHAI]) VALUES (3, N'Có khách')
INSERT [dbo].[TRANGTHAIPHONG] ([MATTP], [TRANGTHAI]) VALUES (4, N'Đang dọn')
INSERT [dbo].[TRANGTHAIPHONG] ([MATTP], [TRANGTHAI]) VALUES (5, N'Đang bảo trì')
INSERT [dbo].[TRANGTHAIPHONG] ([MATTP], [TRANGTHAI]) VALUES (6, N'Không bán')
INSERT [dbo].[TRANGTHAIPHONG] ([MATTP], [TRANGTHAI]) VALUES (7, N'Dơ')
